package com.file.parser;

import com.file.bo.ResponseData;
import com.file.bo.mail.CMBCorp;
import com.file.bo.mail.CMBCorpSummary;
import com.file.bo.mail.CMBCorpTran;
import com.file.constant.ErrorCode;
import com.file.util.JsonUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;

import java.io.File;
import java.io.FileInputStream;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 招商银行公司版 Excel解析
 * @author anyspa
 */

@Slf4j
public class CMBCorpExcelParser {

    public ResponseData<String> parseCMBCorpExcelToJson(String daId, String filePath) {
        log.info("parseCCBExcelToJson started, daId:{}", daId);
        String json = null;

        try {
            CMBCorp cmbcorp = parseCMBCorpExcel(filePath);
            json = JsonUtils.convertObjectToJson(cmbcorp);
        } catch (Exception e) {
            log.error("OnError|{}||{}|{}|{}|{}", ErrorCode.FILE_PARSE_EXCEPTION.getCode(), daId, "", "", "parseCMBCorpExcelToJson failed", e);
            return new ResponseData<>(null, ErrorCode.FILE_PARSE_EXCEPTION.getCode(), ErrorCode.FILE_PARSE_EXCEPTION.getMsg());
        }

        log.info("parseCMBCorpExcelToJson completed, daId:{}", daId);
        return new ResponseData<>(json, ErrorCode.SUCCESS.getCode(), ErrorCode.SUCCESS.getMsg());
    }

    private CMBCorp parseCMBCorpExcel(String filePath) {
        CMBCorp cmbcorp = new CMBCorp();

        try (FileInputStream fis = new FileInputStream(new File(filePath));
             HSSFWorkbook workbook = new HSSFWorkbook(fis)) {
            HSSFSheet sheet = workbook.getSheetAt(0);

            List<CMBCorpTran> cmbcorpTrans = new ArrayList<>();
            for (int i = 0; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (i == 0) {
                    // 2022-07-01至2022-08-11收支记录汇总
                    String transPeriod = row.getCell(0).getStringCellValue();
                    cmbcorp.setTitle(transPeriod);
                    Pattern pattern = Pattern.compile("\\d{4}-\\d{2}-\\d{2}");
                    Matcher matcher = pattern.matcher(transPeriod);
                    if (matcher.find(0)) {
                        cmbcorp.setStartDate(matcher.group());
                    }
                    if (matcher.find(1)) {
                        cmbcorp.setEndDate(matcher.group());
                    }
                }

                if (i >= 2) {
                    CMBCorpTran cmbcorpTran = new CMBCorpTran();
                    cmbcorpTran.setPayerAccount(row.getCell(0).getStringCellValue());
                    cmbcorpTran.setPayerName(row.getCell(1).getStringCellValue());
                    cmbcorpTran.setPayerBank(row.getCell(2).getStringCellValue());
                    cmbcorpTran.setPayerAccountCurrency(row.getCell(3).getStringCellValue());
                    cmbcorpTran.setPayeeAccount(row.getCell(4).getStringCellValue());
                    cmbcorpTran.setPayeeName(row.getCell(5).getStringCellValue());
                    cmbcorpTran.setPayeeBank(row.getCell(6).getStringCellValue());
                    cmbcorpTran.setPayeeAccountCurrency(row.getCell(7).getStringCellValue());
                    cmbcorpTran.setTranAmt(row.getCell(8).getStringCellValue());
                    cmbcorpTran.setBalance(row.getCell(9).getStringCellValue());
                    cmbcorpTran.setTranTime(row.getCell(10).getStringCellValue());
                    cmbcorpTran.setTranSerialNo(row.getCell(11).getStringCellValue());
                    cmbcorpTran.setTranType(row.getCell(12).getStringCellValue());
                    cmbcorpTran.setSummary(row.getCell(13).getStringCellValue());
                    cmbcorpTrans.add(cmbcorpTran);
                }
            }
            cmbcorp.setCmbCorpTrans(cmbcorpTrans);

            CMBCorpSummary cmbCorpSummary = analyzeData(cmbcorpTrans, cmbcorp.getStartDate(), cmbcorp.getEndDate());
            cmbcorp.setCmbCorpSummary(cmbCorpSummary);
            cmbcorp.setStartDate(null);
            cmbcorp.setEndDate(null);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

        return cmbcorp;
    }

    public CMBCorpSummary analyzeData(List<CMBCorpTran> cmbCorpTrans, String startDateStr, String endDateStr) {
        CMBCorpSummary cmbCorpSummary = new CMBCorpSummary();
        BigDecimal totalDebitAmt = new BigDecimal("0.00");
        Integer totalDebitNo = 0;
        BigDecimal recentThirtyDaysTotalDebitAmt = new BigDecimal("0.00");
        Integer recentThirtyDaysTotalDebitNo = 0;

        String recentThirtyDaysEndDateStr = null;
        if (CollectionUtils.isNotEmpty(cmbCorpTrans)) {
            recentThirtyDaysEndDateStr = cmbCorpTrans.get(cmbCorpTrans.size() - 1).getTranTime().substring(0, 10);
        } else {
            recentThirtyDaysEndDateStr = endDateStr;
        }

        String recentThirtyDaysStartDateStr = caculateRecentThirtyDaysStartDateStr(startDateStr, recentThirtyDaysEndDateStr);
        String companyName = null;
        String accountNo = null;

        for (CMBCorpTran cmbCorpTran : cmbCorpTrans) {
            if (cmbCorpTran.getTranAmt().startsWith("-")) {
                if (StringUtils.isBlank(companyName)) {
                    companyName = cmbCorpTran.getPayerName();
                }
                if (StringUtils.isBlank(accountNo)) {
                    accountNo = cmbCorpTran.getPayerAccount();
                }

                BigDecimal debitAmt = new BigDecimal(cmbCorpTran.getTranAmt());

                totalDebitAmt = totalDebitAmt.add(debitAmt);
                totalDebitNo++;

                String tranDateStr = cmbCorpTran.getTranTime().substring(0, 10);
                LocalDate tranDate = LocalDate.parse(tranDateStr);
                LocalDate recentThirtyDaysStartDate = LocalDate.parse(recentThirtyDaysStartDateStr);
                if (!tranDate.isBefore(recentThirtyDaysStartDate)) {
                    recentThirtyDaysTotalDebitAmt = recentThirtyDaysTotalDebitAmt.add(debitAmt);
                    recentThirtyDaysTotalDebitNo++;
                }
            }
        }

        cmbCorpSummary.setCompanyName(companyName);
        cmbCorpSummary.setAccountBank("招商银行");
        cmbCorpSummary.setAccountNo(accountNo);
        cmbCorpSummary.setStartDate(startDateStr);
        cmbCorpSummary.setEndDate(endDateStr);
        cmbCorpSummary.setTotalDebitAmt(String.valueOf(totalDebitAmt));
        cmbCorpSummary.setTotalDebitNo(String.valueOf(totalDebitNo));
        cmbCorpSummary.setRecentThirtyDaysStartDate(recentThirtyDaysStartDateStr);
        cmbCorpSummary.setRecentThirtyDaysEndDate(recentThirtyDaysEndDateStr);
        cmbCorpSummary.setRecentThirtyDaysTotalDebitAmt(String.valueOf(recentThirtyDaysTotalDebitAmt));
        cmbCorpSummary.setRecentThirtyDaysTotalDebitNo(String.valueOf(recentThirtyDaysTotalDebitNo));

        return cmbCorpSummary;
    }

    private String caculateRecentThirtyDaysStartDateStr(String startDateStr, String recentThirtyDaysEndDateStr) {
        LocalDate recentThirtyDaysEndDate = LocalDate.parse(recentThirtyDaysEndDateStr);
        LocalDate startDate = LocalDate.parse(startDateStr);
        LocalDate calStartDate = recentThirtyDaysEndDate.minusDays(29L);
        LocalDate finalStartDate = startDate.isAfter(calStartDate) ? startDate : calStartDate;
        return finalStartDate.toString();
    }

    public String getAccountName(String filePath) {
        String accountName = null;

        try (FileInputStream fis = new FileInputStream(new File(filePath));
             HSSFWorkbook workbook = new HSSFWorkbook(fis)) {
            HSSFSheet sheet = workbook.getSheetAt(0);
            for (int i = 2; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (StringUtils.isNotBlank(row.getCell(8).getStringCellValue()) && row.getCell(8).getStringCellValue().contains("-")) {
                    accountName = row.getCell(1).getStringCellValue();
                    break;
                }
            }

            if (StringUtils.isBlank(accountName)) {
                for (int i = 2; i <= sheet.getLastRowNum(); i++) {
                    Row row = sheet.getRow(i);
                    if (StringUtils.isNotBlank(row.getCell(8).getStringCellValue()) && !row.getCell(8).getStringCellValue().contains("-")) {
                        accountName = row.getCell(5).getStringCellValue();
                        break;
                    }
                }
            }

        } catch (Exception e) {
            throw new RuntimeException(e);
        }

        return accountName;
    }

    public static void main(String[] args) {
//        String filePath = "E:\\data\\files\\CMBCorp\\2022-07-01至2022-08-11收支记录汇总.xls";
//        String filePath = "E:\\data\\files\\CMBCorp\\2022-08-01至2022-08-13收支记录汇总.xls";
        String filePath = "D:\\data\\files\\CMBCorp\\2022-08-01至2022-08-14收支记录汇总.xls";

        CMBCorpExcelParser cmbcorpExcelParser = new CMBCorpExcelParser();
//        ResponseData<String> responseData = cmbcorpExcelParser.parseCMBCorpExcelToJson("dd", filePath);
//        System.out.println(responseData.getData());
        String accountName = cmbcorpExcelParser.getAccountName(filePath);
        System.out.println(accountName);
    }
}
